--    Program: 		CAPPOIMP_APPROVED_SUPPLIER_LIST.sql
--    Description:  Import PO APPROVED SUPPLIER LIST information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_asl_id					number;
                    		                 		
   cursor cur_po_approved_supplier_list is
      select * from CRST_PO_APPROVED_SUPPLIER_LIST
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_po_approved_supplier_list loop   	
     l_err_msg_tmp := '';
     
     select PO_APPROVED_SUPPLIER_LIST_S.NEXTVAL 
     into l_asl_id
     FROM DUAL;
     
     update CRST_PO_APPROVED_SUPPLIER_LIST
     set    asl_id_n = l_asl_id
     where  asl_id = cur.ASL_ID;
     
     begin
       -- import data
       insert into PO_APPROVED_SUPPLIER_LIST
       (
          ASL_ID                  ,
          USING_ORGANIZATION_ID   ,
          OWNING_ORGANIZATION_ID  ,
          VENDOR_BUSINESS_TYPE    ,
          ASL_STATUS_ID           ,
          LAST_UPDATE_DATE        ,
          LAST_UPDATED_BY         ,
          CREATION_DATE           ,
          CREATED_BY              ,
          MANUFACTURER_ID         ,
          VENDOR_ID               ,
          ITEM_ID                 ,
          CATEGORY_ID             ,
          VENDOR_SITE_ID          ,
          PRIMARY_VENDOR_ITEM     ,
          MANUFACTURER_ASL_ID     ,
          REVIEW_BY_DATE          ,
          COMMENTS                ,
          ATTRIBUTE_CATEGORY      ,
          ATTRIBUTE1              ,
          ATTRIBUTE2              ,
          ATTRIBUTE3              ,
          ATTRIBUTE4              ,
          ATTRIBUTE5              ,
          ATTRIBUTE6              ,
          ATTRIBUTE7              ,
          ATTRIBUTE8              ,
          ATTRIBUTE9              ,
          ATTRIBUTE10             ,
          ATTRIBUTE11             ,
          ATTRIBUTE12             ,
          ATTRIBUTE13             ,
          ATTRIBUTE14             ,
          ATTRIBUTE15             ,
          LAST_UPDATE_LOGIN       ,
          REQUEST_ID              ,
          PROGRAM_APPLICATION_ID  ,
          PROGRAM_ID              ,
          PROGRAM_UPDATE_DATE     ,
          DISABLE_FLAG             
       )
       values
       (
            l_asl_id                    ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.USING_ORGANIZATION_ID),
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.OWNING_ORGANIZATION_ID),
            cur.VENDOR_BUSINESS_TYPE    ,
            cur.ASL_STATUS_ID           ,
            cur.LAST_UPDATE_DATE        ,
            CUR.LAST_UPDATED_BY,
            cur.CREATION_DATE           ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.MANUFACTURER_ID         ,
            CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
            --CAPPOIMP_MAPPING.GetItemIDMapping(cur.USING_ORGANIZATION_ID, cur.ITEM_ID),
            CAPPOIMP_MAPPING.GetItemIDMapping(cur.ITEM_ID),
            cur.CATEGORY_ID             ,
            CAPPOIMP_MAPPING.GetVendorSiteIDMapping(cur.VENDOR_SITE_ID),
            cur.PRIMARY_VENDOR_ITEM     ,
            cur.MANUFACTURER_ASL_ID     ,
            cur.REVIEW_BY_DATE          ,
            cur.COMMENTS                ,
            cur.ATTRIBUTE_CATEGORY      ,
            cur.ATTRIBUTE1              ,
            cur.ATTRIBUTE2              ,
            cur.ATTRIBUTE3              ,
            cur.ATTRIBUTE4              ,
            cur.ATTRIBUTE5              ,
            cur.ATTRIBUTE6              ,
            cur.ATTRIBUTE7              ,
            cur.ATTRIBUTE8              ,
            cur.ATTRIBUTE9              ,
            cur.ATTRIBUTE10             ,
            cur.ATTRIBUTE11             ,
            cur.ATTRIBUTE12             ,
            cur.ATTRIBUTE13             ,
            cur.ATTRIBUTE14             ,
            cur.ATTRIBUTE15             ,
            cur.LAST_UPDATE_LOGIN       ,
            cur.REQUEST_ID              ,
            cur.PROGRAM_APPLICATION_ID  ,
            cur.PROGRAM_ID              ,
            cur.PROGRAM_UPDATE_DATE     ,
            cur.DISABLE_FLAG            
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPO_APPROVED_SUPPLIER_LIST', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
